package com.foreveredu.propms.persist.hibernate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.jdbc.core.SqlTypeValue;

import com.foreveredu.propms.domain.House;
import com.foreveredu.propms.domain.Owner;
import com.foreveredu.propms.domain.PageModel;
import com.foreveredu.propms.domain.SubDistrict;
import com.foreveredu.propms.persist.ISubDistrictDao;

public class HibernateSubDistrictDao extends HibernateAbstractDao<SubDistrict, Integer>
		implements ISubDistrictDao {

	private static final long serialVersionUID = 1L;

	public HibernateSubDistrictDao() {
		super(SubDistrict.class);
	}

	//查找某物业管理区下的所有小区
	@SuppressWarnings("unchecked")
	@Override
	public List<SubDistrict> findSubDistrictsOfDistrict(int districtId) {
		return this.getHibernateTemplate().find("from SubDistrict s where s.district.id = ?", districtId);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public SubDistrict findSubDistrictsOfSubDistrictId(String subDistrictId) {
		List<SubDistrict> subDistrictList = this.getHibernateTemplate().find("from SubDistrict s where s.subDistrictId= ?", subDistrictId);
		SubDistrict subDistrict = null;
		if (subDistrictList != null && subDistrictList.size() > 0) {
			subDistrict = subDistrictList.get(0);
		}
		return subDistrict;
	}

	@Override
	public Map<String, Object> querySubPaidRate(SubDistrict sub, int year, int month) {
		Map<String, Object> queryMap = new HashMap<String, Object>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			cstmt = conn.prepareCall("{call proc_querySubPaidRate(?,?,?,?,?)}");
			cstmt.setInt(1, sub.getId());
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.registerOutParameter(4, Types.INTEGER);
			cstmt.registerOutParameter(5, Types.INTEGER);
			cstmt.execute();
			int paid = cstmt.getInt(4);
			int total = cstmt.getInt(5);
			String rate = new DecimalFormat("0.00").format((float)paid/total);
			queryMap.put("paid", String.valueOf(paid));
			queryMap.put("total", String.valueOf(total));
			queryMap.put("rate", rate);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return queryMap;
	}

	@Override
	public Map<String, Object> querySubMonthlyCharge(SubDistrict sub, int year, int month) {
		Map<String, Object> queryMap = new HashMap<String, Object>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			//只根据年份查询时，即month=-1时,调用proc_querySubYearlyCharge存储过程
			if(month == -1){
				cstmt = conn.prepareCall("{call proc_querySubYearlyCharge(?,?,?,?,?,?,?,?,?,?,?,?)}");
			}else{
				cstmt = conn.prepareCall("{call proc_querySubMonthlyCharge(?,?,?,?,?,?,?,?,?,?,?,?)}");
			}
			cstmt.setInt(1, sub.getId());
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.registerOutParameter(4, Types.FLOAT);
			cstmt.registerOutParameter(5, Types.FLOAT);
			cstmt.registerOutParameter(6, Types.FLOAT);
			cstmt.registerOutParameter(7, Types.FLOAT);	
			cstmt.registerOutParameter(8, Types.FLOAT);
			cstmt.registerOutParameter(9, Types.FLOAT);
			cstmt.registerOutParameter(10, Types.FLOAT);
			cstmt.registerOutParameter(11, Types.FLOAT);	
			cstmt.registerOutParameter(12, Types.FLOAT);				
			cstmt.execute();
			queryMap.put("totalSum", cstmt.getFloat(4));
			queryMap.put("paidSum", cstmt.getFloat(5));
			queryMap.put("propCharge", cstmt.getFloat(6));
			queryMap.put("powerCharge", cstmt.getFloat(7));
			queryMap.put("waterCharge", cstmt.getFloat(8));
			queryMap.put("carCharge", cstmt.getFloat(9));
			queryMap.put("rubbishCharge", cstmt.getFloat(10));
			queryMap.put("lightCharge", cstmt.getFloat(11));
			queryMap.put("water2ndCharge", cstmt.getFloat(12));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return queryMap;
	}

	/**
	 *根据指定的小区查询用户
	 */
	
	
	
	public House findByHouseId(String houseId) {
		List<House> houseList = this.getHibernateTemplate().find("from House h where h.houseId= ?", houseId);
		House house = null;
		if (houseList != null && houseList.size() > 0) {
			house = houseList.get(0);
		}
		return house;
	}
	

	@SuppressWarnings("unchecked")
	@Override
	public PageModel queryIndividualCharge(SubDistrict sub, int year,
			int month, int offset, int chargeItemId) {
		System.out.println("最开始的offset是:"+offset);
		/**
		 * 查询用户
		 */
		List<Map<String, Object>> queryList = new ArrayList<Map<String,Object>>();
		List<House> houseList = null;
		List<House> houseLists = null;//分割出来的房子集合
		PageModel pm = new PageModel();
		
		if (sub.getId() == 0) {
			//如果没有选择物业服务处，则小区的ID为0。所以查询所有的小区
			houseList = this.getHibernateTemplate().find("from House h order by h.id");
		} else {
			houseList = this.getHibernateTemplate().find("from House h where " +
				"h.building.subDistrict.id=?",sub.getId());
		}
		if (houseList != null && houseList.size() > 0) {
			pm.setTotal(houseList.size());
			//如果显示的一页条数没有达到10条，则现实目标条数到数组的尾端
			if (offset + 10 > houseList.size()) {
				houseLists=houseList.subList(offset, houseList.size());
			}else{
				//获得截取的数组
				houseLists=houseList.subList(offset, offset + 10);
			}
		}
		/**
		 * 根据上面返回的房子，查询房子的拥有者
		 */
		List<Owner> ownerList=new ArrayList<Owner>();
		for(House house:houseLists){
			ownerList.add(house.getOwner());
		}
		System.out.println("共有多少用户"+ownerList.size());
		Map<String, Object> queryMap = new HashMap<String, Object>();
		//获取户主的信息列表
		if(ownerList==null){
			return null;
		}else{
			for(Owner owner:ownerList){
				queryMap = new HashMap<String, Object>();
				Connection conn = this.getSession().connection();
				CallableStatement cstmt;
				try {
					cstmt = conn.prepareCall("{call proc_queryIndividualCharge(?,?,?,?,?,?)}");
					cstmt.setInt(1, sub.getId());
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.setInt(4, chargeItemId);
					cstmt.setInt(5, owner.getId());
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.execute();
					queryMap.put("resultCharge", cstmt.getFloat(6));
					queryMap.put("ownerName",owner.getName());
					queryList.add(queryMap);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println(queryMap.get("paidSum")+"需要的数据");
			}
		}
		System.out.println("第多少条数据"+offset);
		System.out.println("小区ID"+sub.getId());
		System.out.println("共有多少数据"+houseList.size());
		pm.setDatas(queryList);
		return pm;
	}
	
	
	
	
	//根据小区处查询所有用户缴费明细
	@SuppressWarnings("unchecked")
	@Override
	public PageModel queryUserBySub(SubDistrict sub, int year, int month,int offset) {
		/**
		 * 查询用户
		 */
		List<Map<String, Object>> queryList = new ArrayList<Map<String,Object>>();
		List<House> houseList = null;
		List<House> houseLists = null;//分割出来的房子集合
		PageModel pm = new PageModel();
		
		if (sub.getId() == 0) {
			//如果没有选择小区，则查询所有的小区
			houseList = this.getHibernateTemplate().find("from House h order by h.id");
		} else {
			houseList = this.getHibernateTemplate().find("from House h where " +
				"h.building.subDistrict.id=?",sub.getId());
		}
	
		if (houseList != null && houseList.size() > 0) {
			pm.setTotal(houseList.size());
			//如果显示的一页条数没有达到10条，则现实目标条数到数组的尾端
			if (offset + 10 > houseList.size()) {
				houseLists=houseList.subList(offset, houseList.size());
			}else{
				//获得截取的数组
				houseLists=houseList.subList(offset, offset + 10);
			}
		}
		/**
		 * 根据上面返回的房子，查询房子的拥有者
		 */
		List<Owner> ownerList=new ArrayList<Owner>();
		for(House house:houseLists){
			ownerList.add(house.getOwner());
		}
		System.out.println("共有多少用户"+ownerList.size());
		Map<String, Object> queryMap = new HashMap<String, Object>();

//		//获取户主的信息列表
		if(ownerList==null){
			return null;
		}else{
			for(Owner owner:ownerList){
				queryMap = new HashMap<String, Object>();
				Connection conn = this.getSession().connection();
				CallableStatement cstmt;
				try {
					cstmt = conn.prepareCall("{call proc_queryOwnerMonthlyCharge(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					cstmt.setInt(1, sub.getId());
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.setInt(4, owner.getId());
					cstmt.registerOutParameter(5, Types.FLOAT);
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.registerOutParameter(7, Types.FLOAT);	
					cstmt.registerOutParameter(8, Types.FLOAT);
					cstmt.registerOutParameter(9, Types.FLOAT);
					cstmt.registerOutParameter(10, Types.FLOAT);
					cstmt.registerOutParameter(11, Types.FLOAT);	
					cstmt.registerOutParameter(12, Types.FLOAT);		
					cstmt.registerOutParameter(13, Types.FLOAT);
					//6项
					cstmt.registerOutParameter(14, Types.FLOAT);	
					cstmt.registerOutParameter(15, Types.FLOAT);		
					cstmt.registerOutParameter(16, Types.FLOAT);
					cstmt.registerOutParameter(17, Types.FLOAT);	
					cstmt.registerOutParameter(18, Types.FLOAT);		
					cstmt.registerOutParameter(19, Types.FLOAT);
					cstmt.execute();
					queryMap.put("paidSum", cstmt.getFloat(5));
					queryMap.put("propCharge", cstmt.getFloat(6));
					queryMap.put("powerCharge", cstmt.getFloat(7));
					queryMap.put("waterCharge", cstmt.getFloat(8));
					queryMap.put("carCharge", cstmt.getFloat(9));
					queryMap.put("rubbishCharge", cstmt.getFloat(10));
					queryMap.put("lightCharge", cstmt.getFloat(11));
					queryMap.put("water2ndCharge", cstmt.getFloat(12));
					queryMap.put("totalSum", cstmt.getFloat(13));
					queryMap.put("ownerName",owner.getName());
					//6项
					queryMap.put("charge1", cstmt.getFloat(14));
					queryMap.put("charge2", cstmt.getFloat(15));
					queryMap.put("charge3", cstmt.getFloat(16));
					queryMap.put("charge4", cstmt.getFloat(17));
					queryMap.put("charge5", cstmt.getFloat(18));
					queryMap.put("charge6", cstmt.getFloat(19));
					queryList.add(queryMap);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println(queryMap.get("paidSum")+"需要的数据");
			}
		}
		System.out.println("第多少条数据"+offset);
		System.out.println("小区ID"+sub.getId());
		System.out.println("共有多少数据"+houseList.size());
//	
		// TODO Auto-generated method stub
		pm.setDatas(queryList);
		return pm;
	}

	@Override
	public Map<String, Object> querySubMonthlyChargeYear(SubDistrict sub,
			int year) {
		
			Map<String, Object> queryMap = new HashMap<String, Object>();
			Connection conn = this.getSession().connection();
			CallableStatement cstmt;
			try {
				//只根据年份
					cstmt = conn.prepareCall("{call proc_querySubMonthlyChargeYear(?,?,?,?,?,?,?,?,?,?,?)}");
				cstmt.setInt(1, sub.getId());
				cstmt.setInt(2, year);
				cstmt.registerOutParameter(3, Types.FLOAT);
				cstmt.registerOutParameter(4, Types.FLOAT);
				cstmt.registerOutParameter(5, Types.FLOAT);
				cstmt.registerOutParameter(6, Types.FLOAT);	
				cstmt.registerOutParameter(7, Types.FLOAT);
				cstmt.registerOutParameter(8, Types.FLOAT);
				cstmt.registerOutParameter(9, Types.FLOAT);
				cstmt.registerOutParameter(10, Types.FLOAT);	
				cstmt.registerOutParameter(11, Types.FLOAT);				
				cstmt.execute();
				queryMap.put("totalSum", cstmt.getFloat(3));
				queryMap.put("paidSum", cstmt.getFloat(4));
				queryMap.put("propCharge", cstmt.getFloat(5));
				queryMap.put("powerCharge", cstmt.getFloat(6));
				queryMap.put("waterCharge", cstmt.getFloat(7));
				queryMap.put("carCharge", cstmt.getFloat(8));
				queryMap.put("rubbishCharge", cstmt.getFloat(9));
				queryMap.put("lightCharge", cstmt.getFloat(10));
				queryMap.put("water2ndCharge", cstmt.getFloat(11));
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return queryMap;
	
	}
	
	
	//把所有用户缴费的项目导出Excel文档

	//根据小区处查询所有用户缴费明细
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> doQueryownerChargeToExcel(SubDistrict sub, int year, int month) {
		/**
		 * 查询用户
		 */
		List<Map<String, Object>> queryList = new ArrayList<Map<String,Object>>();
		List<House> houseList = null;
		List<House> houseLists = null;//分割出来的房子集合
		PageModel pm = new PageModel();
		
		if (sub.getId() == 0) {
			//如果没有选择小区，则查询所有的小区
			houseList = this.getHibernateTemplate().find("from House h order by h.id");
		} else {
			houseList = this.getHibernateTemplate().find("from House h where " +
				"h.building.subDistrict.id=?",sub.getId());
		}
	
		if (houseList != null && houseList.size() > 0) {
			pm.setTotal(houseList.size());
			//如果显示的一页条数没有达到10条，则现实目标条数到数组的尾端
			/**
			 * 根据上面返回的房子，查询房子的拥有者
			 */
			List<Owner> ownerList=new ArrayList<Owner>();
			for(House house:houseList){
				ownerList.add(house.getOwner());
			}
			System.out.println("共有多少用户"+ownerList.size());
			Map<String, Object> queryMap = new HashMap<String, Object>();

//			//获取户主的信息列表
			if(ownerList==null){
				return null;
			}else{
				for(Owner owner:ownerList){
					queryMap = new HashMap<String, Object>();
					Connection conn = this.getSession().connection();
					CallableStatement cstmt;
					try {
						cstmt = conn.prepareCall("{call proc_queryOwnerMonthlyCharge(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
						cstmt.setInt(1, sub.getId());
						cstmt.setInt(2, year);
						cstmt.setInt(3, month);
						cstmt.setInt(4, owner.getId());
						cstmt.registerOutParameter(5, Types.FLOAT);
						cstmt.registerOutParameter(6, Types.FLOAT);
						cstmt.registerOutParameter(7, Types.FLOAT);	
						cstmt.registerOutParameter(8, Types.FLOAT);
						cstmt.registerOutParameter(9, Types.FLOAT);
						cstmt.registerOutParameter(10, Types.FLOAT);
						cstmt.registerOutParameter(11, Types.FLOAT);	
						cstmt.registerOutParameter(12, Types.FLOAT);		
						cstmt.registerOutParameter(13, Types.FLOAT);
						//6项
						cstmt.registerOutParameter(14, Types.FLOAT);	
						cstmt.registerOutParameter(15, Types.FLOAT);		
						cstmt.registerOutParameter(16, Types.FLOAT);
						cstmt.registerOutParameter(17, Types.FLOAT);	
						cstmt.registerOutParameter(18, Types.FLOAT);		
						cstmt.registerOutParameter(19, Types.FLOAT);
						cstmt.execute();
						queryMap.put("paidSum", cstmt.getFloat(5));
						queryMap.put("propCharge", cstmt.getFloat(6));
						queryMap.put("powerCharge", cstmt.getFloat(7));
						queryMap.put("waterCharge", cstmt.getFloat(8));
						queryMap.put("carCharge", cstmt.getFloat(9));
						queryMap.put("rubbishCharge", cstmt.getFloat(10));
						queryMap.put("lightCharge", cstmt.getFloat(11));
						queryMap.put("water2ndCharge", cstmt.getFloat(12));
						queryMap.put("totalSum", cstmt.getFloat(13));
						queryMap.put("ownerName",owner.getName());
						//6项
						queryMap.put("charge1", cstmt.getFloat(14));
						queryMap.put("charge2", cstmt.getFloat(15));
						queryMap.put("charge3", cstmt.getFloat(16));
						queryMap.put("charge4", cstmt.getFloat(17));
						queryMap.put("charge5", cstmt.getFloat(18));
						queryMap.put("charge6", cstmt.getFloat(19));
						queryList.add(queryMap);
					} catch (SQLException e) {
						e.printStackTrace();
					}
					System.out.println(queryMap.get("paidSum")+"需要的数据");
				}
			}
		}
	
//	
		// TODO Auto-generated method stub
		return queryList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> queryIndividualChargeIntoExcel(SubDistrict sub,
			int year, int month, int chargeItemId) {
		/**
		 * 查询用户
		 */
		List<Map<String, Object>> queryList = new ArrayList<Map<String,Object>>();
		List<House> houseList = null;
		if (sub.getId() == 0) {
			//如果没有选择物业服务处，则小区的ID为0。所以查询所有的小区
			houseList = this.getHibernateTemplate().find("from House h order by h.id");
		} else {
			houseList = this.getHibernateTemplate().find("from House h where " +
				"h.building.subDistrict.id=?",sub.getId());
		}
		/**
		 * 根据上面返回的房子，查询房子的拥有者
		 */
		List<Owner> ownerList=new ArrayList<Owner>();
		for(House house:houseList){
			ownerList.add(house.getOwner());
		}
		System.out.println("共有多少用户"+ownerList.size());
		Map<String, Object> queryMap = new HashMap<String, Object>();
		//获取户主的信息列表
		if(ownerList==null){
			return null;
		}else{
			for(Owner owner:ownerList){
				queryMap = new HashMap<String, Object>();
				Connection conn = this.getSession().connection();
				CallableStatement cstmt;
				try {
					cstmt = conn.prepareCall("{call proc_queryIndividualCharge(?,?,?,?,?,?)}");
					cstmt.setInt(1, sub.getId());
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.setInt(4, chargeItemId);
					cstmt.setInt(5, owner.getId());
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.execute();
					queryMap.put("resultCharge", cstmt.getFloat(6));
					queryMap.put("ownerName",owner.getName());
					queryList.add(queryMap);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println(queryMap.get("paidSum")+"需要的数据");
			}
		}
		return queryList;
	}

}
